package com.hlw.hr_man.daoImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.hlw.hr_man.Model.EmployeeModel;
import com.hlw.hr_man.dao.FindEmpsDao;
import com.hlw.hr_man.util.JDBCUtil;

/**
 * 查询名片夹Dao层实现类
 * @author qiyu
 *
 */
public class FindEmpsDaoImpl implements FindEmpsDao{

	@Override
	public int getEmpsCount(String searchName) {
		int ecount = 0;
		StringBuffer sql = new StringBuffer("select count(e_id) ecount from hr_employee where e_name like ?");
		Connection con = JDBCUtil.getConnection();
		try {
			PreparedStatement   pst =  con.prepareStatement(sql.toString());
			pst.setString(1, "%"+searchName+"%");
			ResultSet  rs = pst.executeQuery();
			if(rs.next()) {
				ecount = rs.getInt("ecount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ecount;
	}

	@Override
	public List<EmployeeModel> getEmps(int start,
			int pageSize, String searchName) {
		List<EmployeeModel>  Employee = new ArrayList<EmployeeModel>();
		Connection   con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("SELECT e_name,e_age,e_sex,e_id ");
		sql.append("FROM hr_employee WHERE e_name like ? ");
		sql.append(" limit ?,?");
		try {
			PreparedStatement   pst =  con.prepareStatement(sql.toString());
			pst.setString(1, "%"+searchName+"%");
			pst.setInt(2, start);
			pst.setInt(3, pageSize);
			ResultSet  rs = pst.executeQuery();
			while(rs.next()) {
				EmployeeModel  s = new EmployeeModel();
				s.setE_id(rs.getInt("e_id"));
				s.setE_name(rs.getString("e_name"));
				s.setE_age(rs.getInt("e_age"));
				s.setE_sex(rs.getString("e_sex"));
				Employee.add(s);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return Employee;
	}
	@Override
	public List<EmployeeModel> getEmps(int e_id, int start,
			int pageSize) {
		List<EmployeeModel>  Employee = new ArrayList<EmployeeModel>();
		Connection   con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select e_name,e_age,e_sex,e_id");
		sql.append(" from hr_employee where e_id = ? ");
		sql.append(" limit ?,?");
		try {
			PreparedStatement   pst =  con.prepareStatement(sql.toString());
			pst.setInt(1, e_id);
			pst.setInt(2, start);
			pst.setInt(3, pageSize);
			ResultSet  rs = pst.executeQuery();
			while(rs.next()) {
				EmployeeModel  s = new EmployeeModel();
				s.setE_id(rs.getInt("e_id"));
				s.setE_name(rs.getString("e_name"));
				s.setE_age(rs.getInt("e_age"));
				s.setE_sex(rs.getString("e_sex"));
				Employee.add(s);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return Employee;
	}


	@Override
	public int getCardsCount(int id) {
		int ecount = 0;
		StringBuffer sql = new StringBuffer("select count(e_id) ecount from hr_cardcase where hr_cardcase.e_id like ?");
		Connection con = JDBCUtil.getConnection();
		try {
			PreparedStatement   pst =  con.prepareStatement(sql.toString());
			pst.setInt(1, id);
			ResultSet  rs = pst.executeQuery();
			if(rs.next()) {
				ecount = rs.getInt("ecount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ecount;
	}

	@Override
	public EmployeeModel getCardCase(int id) {
		EmployeeModel  s = new EmployeeModel();
		Connection   con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select e_name,e_age,e_sex,card_company,card_address,card_tel,card_email,hr_cardcase.card_id ");
		sql.append(" FROM hr_employee,hr_cardcase WHERE hr_employee.e_id=hr_cardcase.e_id and hr_employee.e_id = ?");
		try {
			PreparedStatement   pst =  con.prepareStatement(sql.toString());
			pst.setInt(1, id);
			ResultSet  rs = pst.executeQuery();
			while(rs.next()) {
				s.setCard_id(rs.getInt("card_id"));
				s.setE_name(rs.getString("e_name"));
				s.setE_age(rs.getInt("e_age"));
				s.setE_sex(rs.getString("e_sex"));
				s.setCard_address(rs.getString("card_address"));
				s.setCard_company(rs.getString("card_company"));
				s.setCard_email(rs.getString("card_email"));
				s.setCard_tel(rs.getString("card_tel"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return s;
	}
}
